package cn.tedu.baidashi.mapper;

import cn.tedu.baidashi.model.Address;
import cn.tedu.baidashi.model.City;
import cn.tedu.baidashi.model.Province;
import cn.tedu.baidashi.model.Zone;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author tedu.cn
 * @since 2022-02-19
 */
@Repository
public interface OrderAddressMapper extends BaseMapper<Address> {
    // 根据用户名获取所有地址
    @Select("SELECT * FROM address  LEFT JOIN customer " +
            " ON  address.customer_id=customer.id WHERE  customer.customer_name=#{customerName}")
    List<Address> getAddressesByUserName(String customerName);

    // 根据用户名和addressId获取地址
    @Select("SELECT * FROM address  LEFT JOIN customer " +
            " ON  address.customer_id=customer.id WHERE address.id=#{addressId} and customer.customer_name=#{customerName}")
    Address getAddressByUserNameAndAddressId(Integer addressId,String customerName);

    // 获取所有省
    @Select("select id,province_name from province")
    List<Province> getAllProvinces();

    //根据省id获取所有市
    @Select("SELECT city.id,city.city_name FROM city  LEFT JOIN province_city ON city.id=province_city.city_id\n" +
            "LEFT JOIN province ON province.id=province_city.province_id WHERE province.id=#{provinceId}")
    List<City> getCitysByProvinceId(Integer provinceId);

    @Select("SELECT city.city_name,city.id FROM city " +
            "LEFT JOIN province_city ON city.id=province_city.city_id " +
            "WHERE city.id in(SELECT city_id FROM province_city " +
            "LEFT JOIN province ON province.id=province_city.province_id " +
            "WHERE province_id=(SELECT province.id FROM province " +
            "WHERE province.province_name=#{provinceName}));")
    List<City> getCitysByProvinceName(String provinceName);

    //根据市id获取所有县\区
    @Select("SELECT zone.id,zone.zone_name FROM zone \" +\n" +
            "                \"LEFT JOIN city_zone ON zone.id=city_zone.zone_id \" +\n" +
            "                \"WHERE zone.id IN (SELECT zone_id FROM city_zone \" +\n" +
            "                \"WHERE city_id=(SELECT city.id FROM city \" +\n" +
            "                \"WHERE city.city_id=#{cityId}));")
    List<Zone> getZonesByCityId(Integer cityId);

    // 根据市名获取所有县\区
    @Select("SELECT zone.id,zone.zone_name FROM zone " +
            "LEFT JOIN city_zone ON zone.id=city_zone.zone_id " +
            "WHERE zone.id IN (SELECT zone_id FROM city_zone " +
            "WHERE city_id=(SELECT city.id FROM city " +
            "WHERE city.city_name=#{cityName}));")
    List<Zone> getZonesByCityName(String cityName);
}
